<?php

namespace TuxCocktail\TuxDb\Mysql\Cocktail;

use TuxCocktail\TuxDb\Mysql\TuxDbMysqlAbstract;
use TuxCocktail\TuxDb\TuxCocktailInterface;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\CocktailTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\DrinkBrandTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\DrinkBrand;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\DrinkFlavor;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\DrinkFlavorTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\DrinkCompanyTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\PouringSpeedTypeTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\PouringLocationTypeTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\DrinkFlavorTypeTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\DrinkTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\CocktailStepTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\CocktailStepTypeTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\PouringTechniqueTypeTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\CocktailSurveTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\SurveAttrTypeTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\GlassTypeTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\SurveAttrTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\GlobalImageTable;
use Zend\Db\Sql\Expression;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\GlobalImageLinkTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\GlobalImageLinkPendingTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\DrinkSumTypeTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\CocktailStepParamTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\MeasurementTypeTable;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\CocktailStepParamTypeTable;
use TuxDrink\TuxDb\Mysql\Drink\TuxDbMysqlDrink;
use TuxCocktail\TuxDb\Mysql\Cocktail\Model\CocktailSourceRelationTable;
use TuxDrink\TuxDb\Mysql\Drink\TuxDbMysqlDrinkGeneric;

class TuxDbMysqlCocktail extends TuxDbMysqlAbstract implements TuxCocktailInterface {
	
	
	protected $supportedImageMimeTypes = array('image/gif','image/jpeg','image/png','image/bmp','image/tiff');
	
	/**
	 * 
	 * @var TuxDbMysqlDrink
	 */
	private $tuxDbMysqldrink;
	
	/**
	 * 
	 * @var TuxDbMysqlDrinkGeneric
	 */
	private $tuxDbMysqlDrinkGeneric;
	
	private $config;
	
	public function __construct($config,$tuxDbMysqDrink,$tuxDbMysqlDrinkGenric) {
		parent::__construct($config);
		$this->tuxDbMysqldrink=$tuxDbMysqDrink;
		$this->tuxDbMysqlDrinkGeneric=$tuxDbMysqlDrinkGenric;
		$this->config=$config;
	}	
	/**
	 * 
	 * @var SurveAttrTypeTable
	 */
	private $surveAttrTypeTable;
	
	
/**
 * 
 * @return \TuxCocktail\TuxDb\Mysql\Cocktail\Model\SurveAttrTypeTable
 */
	function getSurveAttrTypeTable() {
		if (!$this->surveAttrTypeTable) {
			$this->surveAttrTypeTable = new SurveAttrTypeTable($this->tuxDb->getInstance());
		}
		return $this->surveAttrTypeTable;
	}
	
	/**
	 * 
	 * @var PouringTechniqueTypeTable
	 */
	private $pouringTechinqueTypeTable;
	
	function getPouringTechniqueTypetable() {
		if (!$this->pouringTechinqueTypeTable) {
			$this->pouringTechinqueTypeTable = new PouringTechniqueTypeTable($this->tuxDb->getInstance());
		}
		return $this->pouringTechinqueTypeTable;
	}
	
	/**
	 * 
	 * @var CocktailStepTypeTable
	 */
	private $cocktailStepTypeTable;
	
	private function getCocktailStepTypetable() {
		if (!$this->cocktailStepTypeTable) {
			$this->cocktailStepTypeTable = new CocktailStepTypeTable($this->tuxDb->getInstance());
		}
		return $this->cocktailStepTypeTable;
	}
	
	/**
	 * 
	 * @var CocktailStepTable
	 */
	private $cocktaiLStepTable;
	
	
	private function getCocktailSteptable() {
		if (!$this->cocktaiLStepTable) {
			$this->cocktaiLStepTable = new CocktailStepTable($this->tuxDb->getInstance());
		}
		return $this->cocktaiLStepTable;
	}
	
	/**
	 * 
	 * @var DrinkTable
	 * 
	 */
	private $drinkTable;
	
	
	private function getDrinkTable() {
		if (!$this->drinkTable) {
			$this->drinkTable = new DrinkTable($this->tuxDb->getInstance());
		}
		return $this->drinkTable;
	}
	
	/**
	 * 
	 * @var CocktailTable
	 */
	private $cocktailTable;
	
	private function getCocktailTable() {
		if (!$this->cocktailTable) {
			$this->cocktailTable = new CocktailTable($this->tuxDb->getInstance());
		}
		return $this->cocktailTable;
	}
	
	/**
	 * 
	 * @var DrinkBrandTable
	 */
	private $drinkBrandTable;
	
	private function getDrinkBrandTable() {
		if (!$this->drinkBrandTable) {
			$this->drinkBrandTable = new DrinkBrandTable($this->tuxDb->getInstance());
		}
		return $this->drinkBrandTable;
	}
	
	/**
	 * 
	 * @var DrinkFlavorTable
	 */
	private $drinkFlavorTable;
	
	private function getDrinkFlavorTable() {
		if (!$this->drinkFlavorTable) {
			$this->drinkFlavorTable = new DrinkFlavorTable($this->tuxDb->getInstance());
		}
		return $this->drinkFlavorTable;
	}
	
	/**
	 * 
	 * @var DrinkFlavorTypeTable
	 */
	private $drinkFlavorTypeTable;
	
	
	private function getDrinkFlavorTypeTable() {
		if (!$this->drinkFlavorTypeTable) {
			$this->drinkFlavorTypeTable = new DrinkFlavorTypeTable($this->tuxDb->getInstance());
		}
		return $this->drinkFlavorTypeTable;
	}
	
	/**
	 * 
	 * @var DrinkCompanyTable
	 */
	private $drinkCompanyTable;
	
	private function getDrinkCompanyTable() {
		if (!$this->drinkCompanyTable) {
			$this->drinkCompanyTable = new DrinkCompanyTable($this->tuxDb->getInstance());
		}
		return $this->drinkCompanyTable;
	}
	
	/**
	 * 
	 * @var PouringSpeedTypeTable
	 */
	private $pouringSpeedTypeTable;
	
	private function getPouringSpeedTypeTable() {
		if (!$this->pouringSpeedTypeTable) {
			$this->pouringSpeedTypeTable = new PouringSpeedTypeTable($this->tuxDb->getInstance());
		}
		return $this->pouringSpeedTypeTable;
	}
	
	/**
	 * 
	 * @var SurveAttrTable
	 */
	public $surveAttrTable;
	
	private function getSurveAttrTable() {
		if (!$this->surveAttrTable) {
			$this->surveAttrTable = new SurveAttrTable($this->tuxDb->getInstance());
		}
		return $this->surveAttrTable;
	}
	
	/**
	 * 
	 * @var GlassTypeTable
	 */
	private $glassTypeTable;
	
	/**
	 * 
	 * @return \TuxCocktail\TuxDb\Mysql\Cocktail\Model\GlassTypeTable
	 */
	private function getGlassTypeTable() {
		if (!$this->glassTypeTable) {
			$this->glassTypeTable = new GlassTypeTable($this->tuxDb->getInstance());
		}
		return $this->glassTypeTable;
 	}
 	
 	
 	/**
 	 * 
 	 * @var GlobalImageTable
 	 */
 	private $globalImageTable;
 	
 	private function getGlobalImageTable() {
 		if (!$this->globalImageTable) {
 			$this->globalImageTable = new GlobalImageTable($this->tuxDb->getInstance());
 		}
 		return $this->globalImageTable;
 	}
 	
 	/**
 	 * 
 	 * @var GlobalImageLinkTable
 	 */
 	private $globalImageLinkTable;
 	
 	private function getGlobalImageLinkTable() {
 		if (!$this->globalImageLinkTable) {
 			$this->globalImageLinkTable = new GlobalImageLinkTable($this->tuxDb->getInstance());
 		}
 		return $this->globalImageLinkTable;
 	}
 	
 	/**
 	 * 
 	 * @var GlobalImageLinkPendingTable
 	 */
 	private $globalImageLinkPendingTable;
 	
 	/**
 	 * 
 	 * @return \TuxCocktail\TuxDb\Mysql\Cocktail\Model\GlobalImageLinkPendingTable
 	 */
 	private function getGlobalImageLinkPendingTable() {
 		if (!$this->globalImageLinkPendingTable) {
 			$this->globalImageLinkPendingTable = new GlobalImageLinkPendingTable($this->tuxDb->getInstance());
 		}
 		return $this->globalImageLinkPendingTable;
 	}
 	
 	/**
 	 * 
 	 * @var DrinkSumTypeTable
 	 */
 	private $drinkSumTypeTable;
 	
 	private function getDrinkSumTypeTable() {
 		if (!$this->drinkSumTypeTable) {
 			$this->drinkSumTypeTable = new DrinkSumTypeTable($this->tuxDb->getInstance());
 		}
 		return $this->drinkSumTypeTable;
 	}
 	
 	/**
 	 *
 	 * @var PouringLocationTypeTable
 	 */
 	private $pouringLocationTypeTable;
 	
 	private function getPouringLocationTypeTable() {
 		if (!$this->pouringLocationTypeTable) {
 			$this->pouringLocationTypeTable = new PouringLocationTypeTable($this->tuxDb->getInstance());
 		}
 		return $this->pouringLocationTypeTable;
 	} 	
 	
 	private $cocktailStepParamTable;
 	
 	private function getCocktailStepParamTable() {
 		if (!$this->cocktailStepParamTable) {
 			$this->cocktailStepParamTable = new CocktailStepParamTable($this->tuxDb->getInstance());
 		}
 		return $this->cocktailStepParamTable;
 	}
 	
 	/**
 	 * 
 	 * @var MeasurementTypeTable
 	 */
 	private $measurementTypeTable;
 	
 	private function getMeasurementTypeTable() {
 		if (!$this->measurementTypeTable) {
 			$this->measurementTypeTable= new MeasurementTypeTable($this->tuxDb->getInstance());
 		}
 		return $this->measurementTypeTable;
 	}
 	
 	/**
 	 * 
 	 * @var CocktailStepParamTypeTable
 	 */
 	private $cocktailStepParamTypeTable;
 	
 	private function getCocktailStepParamTypeTable() {
 		if (!$this->cocktailStepParamTypeTable) {
 			$this->cocktailStepParamTypeTable = new CocktailStepParamTypeTable($this->tuxDb->getInstance());
 		}
 		return $this->cocktailStepParamTypeTable;
 	}
 	
 	
 	/**
 	 * 
 	 * @var CocktailSourceRelationTable
 	 */
 	private $cocktailSourceRelationTable;
 	
 	private function getCocktailSourceRelationTable() {
 		if (!$this->cocktailSourceRelationTable) {
 			$this->cocktailSourceRelationTable = new CocktailSourceRelationTable($this->tuxDb->getInstance());
 		}
 		return $this->cocktailSourceRelationTable;
 	}
 	
 	/**
 	 * 
 	 * @var TuxDbMysqlGlassType
 	 */
 	private $glass;
 	
 	private function getGlass() {
 		if (!$this->glass) {
 			$this->glass = new TuxDbMysqlGlassType($this->config);
 		}
 		return $this->glass;
 	}
 	
 	/**
 	 * 
 	 * @var TuxDbMysqlShakerType
 	 */
 	private $shaker;
 	
 	private function getShaker() {
 		if (!$this->shaker) {
 			$this->shaker = new TuxDbMysqlShakerType($this->config);
 		}
 		return $this->shaker;
 	}
 	
 	/**
 	 * 
 	 * @var TuxDbMysqlSurveAddins
 	 */
 	private $addins;
 	
 	private function getAddins() {
 		if (!$this->addins) {
 			$this->addins = new TuxDbMysqlSurveAddins($this->config);
 		}
 		return $this->addins;
 	}
 	
 	public function getDrinkSumTypes() {
 		$drinkSumTypeTable = $this->getDrinkSumTypeTable();
 		$select = $drinkSumTypeTable->newSelect();
 		$select = $drinkSumTypeTable->getSelect($select,array('Name'=>'drink_sum_type_name','IsMeasurable'=>'drink_sum_is_measurable'));
 		$resultSet = $drinkSumTypeTable->selectWith($select);
 		if ($resultSet->count() == 0) {
 			return null;
 		} else {
 			return $resultSet->toArray();
 		}
 	}
 	
 	public function getGlassTypeId($glassTypeName) {
 		$glassTypeTable = $this->getGlassTypeTable();
 		$select = $glassTypeTable->newSelect();
 		$glassTypeTable->getSelect($select,array('glass_type_id'))->where(array('glass_name'=>$glassTypeName));
 		$resultSet = $glassTypeTable->selectWith($select);
 		if ($resultSet->count() == 1) {
 			$row = $resultSet->current();
 			return $row->glass_type_id;
 		}
 		return null;
 	}
 	
 	public function updateCocktailName($cocktailId,$cocktailName) {
 		$this->getCocktailTable()->update(array('cocktail_name'=>$cocktailName),array('cocktail_id'=>$cocktailId));
 	}
	
 	public function createCocktail($name,$userId) {
 		$cocktailTable = $this->getCocktailTable();
 		$affectedRows = $cocktailTable->addCocktail($name,$userId);
 		if ($affectedRows == null) {
 			return null;
 		}
 		$cocktailId = $cocktailTable->lastInsertValue;
 		return $cocktailId;
 	}
 	
	public function createCocktailOld($name,$desc,$glassTypeName,$pictureFile=null,$previewImageRotation=null) {
		$cocktailTable = $this->getCocktailTable();
		$glassTypeId = $this->getGlassTypeId($glassTypeName);
		if ($glassTypeId == null) {
			return null;
		}
		$affectedRows = $cocktailTable->addCocktail($name, $glassTypeId,$desc);
		if ($affectedRows == null) {
			return null;
		}
		$cocktailId = $cocktailTable->lastInsertValue;
		
		if ($pictureFile['error'] == 0) { // image uploaded properly
		
			if (in_array($pictureFile['type'],$this->supportedImageMimeTypes)) {
		
				$imageId = $this->getGlobalImageTable()->createEmptyRow();
				$imgAppend=sprintf('%010u',$imageId).'.png';
				$filepath = IMAGES_DIRECTORY . 'image_' . $imgAppend;
				$thumbpath = IMAGES_DIRECTORY . 'thumb_' . $imgAppend;
					
				$im = new \Imagick();
				$im->readImage($pictureFile['tmp_name']);
				$profiles = $im->getImageProfiles("icc", true);
				$im->stripimage();
				if(!empty($profiles)) {
					$im->profileImage("icc", $profiles['icc']);
				}
				if (is_numeric($previewImageRotation) && $previewImageRotation > 0) {
					$im->rotateimage(new \ImagickPixel('#00000000'), $previewImageRotation);
				}
				$result=$im->writeImage($filepath);
					
				if (!$result) {
					error_log("uploaded image could not be moved to the appropriate location");
					//		$this->messanger->addErrorMessage('information was added without the image. there was an error uploading the image. try again later');
				}
		
				$im->resizeImage(100,100, \Imagick::FILTER_LANCZOS, 0.9, true);
				$result=$im->writeImage($thumbpath);
		
				if (!$result) {
					error_log("thumbnail could not be moved to the appropriate location");
					//	$this->messanger->addErrorMessage('information was added without the image. there was an error converting it to a thumbnail. try again later');
		
				}
		
				$im->clear();
				$im->destroy();
		
				$pendingImageLinkData = array (
		
						'global_image_link_pending_imageid'=>$imageId,
						'global_image_link_pending_typeid'=>2,
						'global_image_link_pending_pkey'=>$cocktailId,
		
				);
				$this->getGlobalImageLinkPendingTable()->insert($pendingImageLinkData);
		
			} else {
				error_log('information was added to db without the image, image format not supported');
				//$this->messanger->addMessage('information was added to db without the image, image format not supported');
		
			}
		}
		
		return $cocktailId;
	}
	

	
	public function getCocktailImagesPendingData() {
		$globalImageLinkPendingTable = $this->getGlobalImageLinkPendingTable();
		$select = $globalImageLinkPendingTable->newSelect();
		$globalImageLinkPendingTable->getSelect($select,array('id'=>'global_image_link_pending_id','Image'=>'global_image_link_pending_imageid'))
		->join('cocktail','cocktail.cocktail_id = global_image_link_pending.global_image_link_pending_pkey',array('Name'=>'cocktail_name'))
		->where(array('global_image_link_pending.global_image_link_pending_typeid'=>2));
		$resultSet = $globalImageLinkPendingTable->selectWith($select);
		if ($resultSet->count() == 0) {
			return null;
		} else {
			return $resultSet->toArray();
		}
	}
	
	
	public function addCocktailStep($cocktailid,$stepCount,$cocktailStepTypeId,$measurement,$comment,$params) {
		$cocktailStepTable = $this->getCocktailSteptable();
		$affectedRows = $cocktailStepTable->addCocktailStep($cocktailStepTypeId, $cocktailid, $stepCount,$measurement,$comment);
		if ($affectedRows != 1) {
			throw new \Exception("could not add cocktail step");
		}
		$cocktailStepId=$cocktailStepTable->lastInsertValue;
 		if (is_array($params) && sizeof($params) > 0) {
 			$cocktailStepParamTable = $this->getCocktailStepParamTable();
 			$paramCount=0;
 			foreach ($params as $param) {
				$paramCount++;
 				$cocktailStepParamTable->addCocktailStepParam($cocktailStepId, $paramCount, $param['type_id'], $param['value']);
 			}
 		}
		
	}
	

	
	public function addCocktailStepOld($cocktailId,$stepCount,$drinkFlavorTypeName,
			$drinkBrandName,$drinkCompanyName,$pouringSpeed,$pouringLocation,
			$drinkSumType,$drinkMeasurements) {
		
		$drinkCompanyTable = $this->getDrinkCompanyTable();
		$select = $drinkCompanyTable->newSelect();
		$drinkCompanyTable->getSelect($select,array('drink_company_id'))->where(array('drink_company_name'=>$drinkCompanyName));
		$resultSet = $drinkCompanyTable->selectWith($select);
		$drinkCompanyId=null;
		if ($resultSet->count() > 0) {
			$ret = $resultSet->toArray();
			$drinkCompanyId=$ret[0]['drink_company_id'];
		} else {
			return "could not find drink company name ".$drinkCompanyName;
		}
	
		
		$drinkFlavorTypeTable = $this->getDrinkFlavorTypeTable();
		$select = $drinkFlavorTypeTable->newSelect();
		$drinkFlavorTypeTable->getSelect($select,array('drink_flavor_type_id'))->where(array('drink_flavor_type_name'=>$drinkFlavorTypeName));
		$resultSet = $drinkFlavorTypeTable->selectWith($select);
		$drinkFlavorTypeId=null;
		if ($resultSet->count() > 0) {
			$ret=$resultSet->toArray();
			$drinkFlavorTypeId=$ret[0]['drink_flavor_type_id'];
		} else {
			return "could not find drink flavor type name".$drinkFlavorTypeName;
		}
		
		$drinkBrandTable = $this->getDrinkBrandTable();
		$select = $drinkBrandTable->newSelect();
		$drinkBrandTable->getSelect($select,array('drink_brand_id'))->where(array('drink_brand_name'=>$drinkBrandName,'drink_company_id'=>$drinkCompanyId));
		$resultSet = $drinkBrandTable->selectWith($select);
		$drinkBrandId=null;
		if ($resultSet->count() > 0) {
			$ret=$resultSet->toArray();
			$drinkBrandId=$ret[0]['drink_brand_id'];
		} else {
			return "could not find drink brand ".$drinkBrandName;
		}
		
		$pouringLocationTypeTable=$this->getPouringLocationTypeTable();
		$select = $pouringLocationTypeTable->newSelect();
		$pouringLocationTypeTable->getSelect($select,array('pouring_location_type_id'))->where(array('pouring_location_type_name'=>$pouringLocation));
		$resultSet = $pouringLocationTypeTable->selectWith($select);
		$pouringLocationId=null;
		if ($resultSet->count() >0) {
			$ret=$resultSet->toArray();
			$pouringLocationId=$ret[0]['pouring_location_type_id'];
		} else {
			return "could not find pouring location type name ".$pouringLocation; 
		}
		
		$drinkTable = $this->getDrinkTable();
		$select = $drinkTable->newSelect();
		$drinkTable->getSelect($select,array('drink_id'))->where(array('drink_brand_id'=>$drinkBrandId));
		$resultSet = $drinkTable->selectWith($select);
		$drinkId=null;
		if ($resultSet->count() > 0) {
			$ret = $resultSet->toArray();
			$drinkId=$ret[0]['drink_id'];
		} else {
			return "could not find drink brand id ".$drinkBrandId;
		}
		
		$drinkFlavorTable = $this->getDrinkFlavorTable();
		$select = $drinkFlavorTable->newSelect();
		$drinkFlavorTable->getSelect($select,array('drink_flavor_id'))->where(array('drink_flavor_type_id'=>$drinkFlavorTypeId,'drink_id'=>$drinkId));
		$resultSet = $drinkFlavorTable->selectWith($select);
		$drinkFlavorId=null;
		if ($resultSet->count() > 0) {
			$ret = $resultSet->toArray();
			$drinkFlavorId=$ret[0]['drink_flavor_id'];
		} else {
			return "could not find drink flavor type id ".$drinkFlavorTypeId. " with drink id ".$drinkId;
		}
		
		$pouringSpeedTypeTable = $this->getPouringSpeedTypeTable();
		$select = $pouringSpeedTypeTable->newSelect();
		$pouringSpeedTypeTable->getSelect($select,array('pouring_speed_type_id'))->where(array('pouring_speed_type_name'=>$pouringSpeed));
		$resultSet = $pouringSpeedTypeTable->selectWith($select);
		$pouringSpeedId = null;
		if ($resultSet->count() > 0) {
			$ret = $resultSet->toArray();
			$pouringSpeedId=$ret[0]['pouring_speed_type_id'];
		} else {
			return "could not find pouring speed type name ".$pouringSpeed;
		}
		
		$pouringTechniqueTypeTable=$this->getPouringTechniqueTypetable();
		$pouringTechniqueId = $pouringTechniqueTypeTable->createIfNotExist(array('pouring_location_type_id'=>$pouringLocationId,'pouring_speed_type_id'=>$pouringSpeedId));
		
		$cocktailStepTypeTable = $this->getCocktailStepTypetable();
		$cocktailStepTypeId=$cocktailStepTypeTable->addCocktailStepType($drinkFlavorId, $pouringTechniqueId);

		$drinkSumTypeId = $this->getDrinkSumTypeId($drinkSumType);
		if ($drinkSumTypeId == null) {
			return "could not find drink sum type";
		}
		
		$cocktaiLStepTable=$this->getCocktailSteptable();
		$cocktaiLStepTable->addCocktailStep($cocktailStepTypeId, $cocktailId, $stepCount, $drinkSumTypeId,$drinkMeasurements);
		return true;
	}
	
	public function getDrinkSumTypeId($drinkSumType) {
		$drinkSumTypeTable = $this->getDrinkSumTypeTable();
		$select = $drinkSumTypeTable->newSelect();
		$drinkSumTypeTable->getSelect($select,array('drink_sum_type_id'))->where(array('drink_sum_type_name'=>$drinkSumType));
		$resultSet = $drinkSumTypeTable->selectWith($select);
		$row = $resultSet->current();
		if (!$row) {
			return null;
		} else {
			return $row->drink_sum_type_id;
		}
		
	}
	

	public function getLatestCocktails($limit=null) {
		$theLimit=($limit == null ? 4 : $limit);
		$cocktailTable=  $this->getCocktailTable();
		$select = $cocktailTable->newSelect();
		$cocktailTable->getSelect($select,array('Name'=>'cocktail_name'))
		->join('global_image_link',new Expression('global_image_link.global_image_type_id = 2 and global_image_link.table_pkey = cocktail.cocktail_id'),array('Image'=>'global_image_id'),'left')
            ->join('user','user.user_id=cocktail.user_id',array('Username'=>'username'))
		->order('cocktail_id desc')->limit(intval($theLimit));
		$resultSet = $cocktailTable->selectWith($select);
		if ($resultSet->count() > 0) {
			return $resultSet->toArray();
		}
		return null;
	}
	
	
	
	public function getCocktailId($cocktailName) {
		$cocktailTable = $this->getCocktailTable();
		$select = $cocktailTable->newSelect();
		$cocktailTable->getSelect($select,array('cocktail_id'))->where(array('cocktail_name'=>$cocktailName));
		$resultSet = $cocktailTable->selectWith($select);
		if ($resultSet->count() != 1) {
			return null;
		}
		return $resultSet->toArray()[0]['cocktail_id'];
	}
		
	public function getCocktailInformation($cocktailId) {
		$cocktailTable = $this->getCocktailTable();
		$select = $cocktailTable->newSelect();
		$cocktailTable->getSelect($select,array('id'=>'cocktail_id','Name'=>'cocktail_name'))
		->join('global_image_link',new Expression('global_image_link.global_image_type_id = 2 and global_image_link.table_pkey = cocktail.cocktail_id'),array('Image'=>'global_image_id'),'left')
		->join('cocktail_source_relation','cocktail_source_relation.cocktail_id=cocktail.cocktail_id',array(),'left')
		->join('cocktail_source','cocktail_source.cocktail_source_id = cocktail_source_relation.cocktail_source_id',array('Source'=>'cocktail_source_name'),'left')
		->join('cocktail_source_type','cocktail_source_type.cocktail_source_type_id = cocktail_source.cocktail_source_type_id',array('Source Type'=>'cocktail_source_type_name'),'left')
		->where(array('cocktail.cocktail_id'=>$cocktailId));
		$resultSet = $cocktailTable->selectWith($select);
		if ($resultSet->count() == 0) {
			return null;
		} else {
			return $resultSet->toArray()[0];
		}
		
	}

	public function getCocktailNameIsAvailable($cocktailName) {
		$cocktailTable = $this->getCocktailTable();
		$select = $cocktailTable->newSelect();
		$cocktailTable->getSelect($select,array('cocktail_id'))->where(array('cocktail_name'=>$cocktailName));
		$resultSet = $cocktailTable->selectWith($select);
		$row = $resultSet->current();
		return (!$row);
	}
	
	public function getAllCocktails() {
		$cocktailTable = $this->getCocktailTable();
		$select = $cocktailTable->newSelect();
		$cocktailTable->getSelect($select,array('id'=>'cocktail_id','Name'=>'cocktail_name'))
		->join('global_image_link',new Expression('global_image_link.global_image_type_id = 2 and global_image_link.table_pkey = cocktail.cocktail_id'), array('Image'=>'global_image_id'),'left');
		$resultSet = $cocktailTable->selectWith($select);
		if ($resultSet->count() == 0) {
			return null;
		}
		return json_encode($resultSet->toArray());
	
	}
	
	public function getCocktailSteps($cocktailId) {
		$cocktailStepTypes = $this->getCocktailStepsTypes();
		$cocktailStepParamTypes=$this->getCocktailStepParamTypes();
		$cocktailStepTable=  $this->getCocktailSteptable();
		$select = $cocktailStepTable->newSelect();
		$cocktailStepTable->getSelect($select,array('cocktail_step_type_id',
				'step_params_type'=>new Expression('group_concat(cocktail_step_param_type_id order by cocktail_step_param_count)'),
				'step_params_value'=>new Expression('group_concat(cocktail_step_param_value order by cocktail_step_param_count)')))
		->join('cocktail_step_param', 'cocktail_step_param.cocktail_step_id = cocktail_step.cocktail_step_id')
		->where(array('cocktail_id'=>$cocktailId))
		->group('cocktail_step.cocktail_step_id')
		->order('cocktail_step_count');
		$resultSet = $cocktailStepTable->selectWith($select);
		$steps=array();
		foreach($resultSet as $row) {
			$cocktailStepTypeId=$row->cocktail_step_type_id;
			$stepParamsType=$row->step_params_type;
			$stepParamsValue=$row->step_params_value;
			
			$stepsParamsTypeArray=explode(',', $stepParamsType);
			$stepsParamsValueArray=explode(',', $stepParamsValue);
			
			if (sizeof($stepsParamsTypeArray) != sizeof($stepsParamsValueArray)) {
				throw new \Exception("size of steps params and values differ");
			}			
			
			$params=array();
			
			for ($i=0;$i<sizeof($stepsParamsTypeArray);$i++) {
				$currentStepParamType = $stepsParamsTypeArray[$i];
				$currentStepParamValue = $stepsParamsValueArray[$i];
				
				switch ($currentStepParamType) {
					case 1:
						array_push($params,$this->tuxDbMysqldrink->getFullDrinkName($currentStepParamValue));
						break;
					case 2:
						array_push($params,$this->getGlass()->getGlassTypeName($currentStepParamValue));
						break;
					case 3:
						array_push($params,$this->getShaker()->getShakerTypeName($currentStepParamValue));
						break;
					case 4:
						array_push($params,$this->getAddins()->getSurveAttrName($currentStepParamValue));
						break;
					case 5:
						array_push($params,$this->getMeasurementTypeName($currentStepParamValue));
						break;
					case 6:
						array_push($params,$currentStepParamValue);
						break;
					case 7:
						array_push($params,$this->tuxDbMysqlDrinkGeneric->getFullDrinkName($currentStepParamValue));
						break;
					default:
						break;
				}
				
			}
			$string = $cocktailStepTypes[$cocktailStepTypeId][1];
			for ($i=0;$i<sizeof($params);$i++) {
				$string = str_replace('$' . ($i+1), $params[$i], $string);
			}
			array_push($steps,$string);
		}
		return $steps;
	} 
	
	public function getMeasurementTypeName($measurementTypeId) {
		$measurementTypeTable = $this->getMeasurementTypeTable();
		$select = $measurementTypeTable->newSelect();
		$measurementTypeTable->getSelect($select,array('measurement_type_name'))->where(array('measurement_type_id'=>$measurementTypeId));
		$resultSet = $measurementTypeTable->selectWith($select);
		if ($resultSet->count() != 1) {
			return null;
		}
		return $resultSet->toArray()[0]['measurement_type_name'];
	}
	
	public function getCocktailStepsOld($cocktailId) {
		$cocktailStepTable = $this->getCocktailSteptable();
		$select = $cocktailStepTable->newSelect();
		$cocktailStepTable->getSelect($select,array('Step'=>'cocktail_step_count'))
		->join('cocktail_step_type','cocktail_step_type.cocktail_step_type_id = cocktail_step.cocktail_step_type_id',array(),'left')
		->join('drink_flavor','drink_flavor.drink_flavor_id = cocktail_step_type.drink_flavor_id',array(),'left')
		->join('drink','drink.drink_id = drink_flavor.drink_id',array(),'left')
		->join('drink_flavor_type','drink_flavor_type.drink_flavor_type_id=drink_flavor.drink_flavor_type_id',array('Flavor'=>'drink_flavor_type_name'),'left')
		->join('global_image_link',new Expression('global_image_link.global_image_type_id = 2 and global_image_link.table_pkey = drink_flavor.drink_flavor_id'), array('Image'=>'global_image_id'),'left')
		->join('drink_type','drink_type.drink_type_id = drink.drink_type_id',array('Type'=>'drink_type_name'),'left')
		->join('drink_brand','drink_brand.drink_brand_id = drink.drink_brand_id',array('Brand'=>'drink_brand_name'),'left')
		->join('drink_company','drink_company.drink_company_id = drink_brand.drink_company_id',array('Company'=>'drink_company_name'),'left')
		->join('pouring_technique_type','pouring_technique_type.pouring_technique_type_id = cocktail_step_type.pouring_technique_type_id',array(),'left')
		->join('pouring_speed_type','pouring_speed_type.pouring_speed_type_id = pouring_technique_type.pouring_speed_type_id',array('Pouring Speed'=>'pouring_speed_type_name'),'left')
		->join('pouring_location_type','pouring_location_type.pouring_location_type_id = pouring_technique_type.pouring_location_type_id',array('Pouring Location'=>'pouring_location_type_name'),'left')
		->where(array('cocktail_step.cocktail_id'=>$cocktailId));
		$resultSet= $cocktailStepTable->selectWith($select);
		if ($resultSet->count() == 0) {
			return null;
		} else {
			return $resultSet->toArray();
		}
	}

	public function getFullCocktailInformation($cocktailId){
		$cocktailInfo = $this->getCocktailInformation($cocktailId);
		$cocktailSteps = $this->getCocktailSteps($cocktailId);
		$cocktailSurveAttributes=$this->getCocktailSurveAttributes($cocktailId);
		$data=array();
		$data['cocktail_info']=$cocktailInfo;
		$data['cocktail_steps']=$cocktailSteps;
		$data['cocktail_surve_attributes']=$cocktailSurveAttributes;
		return $data;
	}
	
	public function findCocktailsByUserInventory($userId) {
		$cocktailTable = $this->getCocktailTable();
		$sql = "select cocktail.cocktail_name as Name,global_image_link.global_image_id as Image
				from cocktail_step_param 
				join cocktail_step on cocktail_step.cocktail_step_id=cocktail_step_param.cocktail_step_id
				join cocktail on cocktail.cocktail_id = cocktail_step.cocktail_id
				left join drink_inventory on cocktail_step_param_type_id=1 and drink_inventory.user_id = ".$cocktailTable->fp('user_id')." and drink_inventory.drink_flavor_id=cocktail_step_param.cocktail_step_param_value
				left join global_image_link on global_image_link.global_image_type_id=2 and global_image_link.table_pkey=cocktail.cocktail_id
				left join (select drink_generic.drink_generic_id as id
from drink_inventory
join drink_flavor on drink_flavor.drink_flavor_id = drink_inventory.drink_flavor_id
join drink on drink.drink_id = drink_flavor.drink_id
join drink_type on drink.drink_type_id = drink_type.drink_type_id
join drink_generic on drink_generic.drink_type_id = drink_type.drink_type_id 
			and drink_generic.drink_flavor_type_id = drink_flavor.drink_flavor_type_id
where drink_inventory.user_id = ".$cocktailTable->fp('user_id').") as generic_id on cocktail_step_param_type_id=7 and generic_id.id = cocktail_step_param.cocktail_step_param_value
				group by cocktail.cocktail_id
				having (count(distinct cocktail_step_param.cocktail_step_param_value)=count(distinct drink_inventory.drink_flavor_id) + count(distinct generic_id.id))
				";
		$statement = $cocktailTable->getAdapter()->query($sql);
		$parameters = array(
			'user_id'=>$userId
		);
		$resultSet=$statement->execute($parameters);
		if ($resultSet->count() > 0) {
			$result=array();
			foreach ($resultSet as $row) {
				array_push($result,$row);
			}
			return $result;
		} else {
			return null;
		}
	}
	
	public function getMeasurementTypes() {
		$measurementTypeTable = $this->getMeasurementTypeTable();
		$select = $measurementTypeTable->newSelect();
		$measurementTypeTable->getSelect($select,array('measurement_type_name'));
		$resultSet = $measurementTypeTable->selectWith($select);
		$result = array();
		foreach ($resultSet as $row) {
			array_push($result,$row->measurement_type_name);
		}
		return $result;
	}

	public function getMeasurementId($measurementTypeName) {
		$measurementTypeTable = $this->getMeasurementTypeTable();
		$select = $measurementTypeTable->newSelect();
		$measurementTypeTable->getSelect($select,array('measurement_type_id'))->where(array('measurement_type_name'=>$measurementTypeName));
		$resultSet=$measurementTypeTable->selectWith($select);
		if ($resultSet->count() != 1) {
			return null;
		}
		$measurementTypeId=$resultSet->toArray()[0]['measurement_type_id'];
		return $measurementTypeId;
	}
	
	public function getCocktailStepsTypes() {
		$cocktailStepsTypes = $this->getCocktailStepTypetable();
		$resultSet = $cocktailStepsTypes->fetchAll();
		$result=array();
		foreach ($resultSet as $row) {
			$result[$row->cocktail_step_type_id]=array($row->cocktail_step_type_regex,$row->cocktail_step_type_string);
		}
		return $result;
	}
	
	public function getCocktailStepParamTypes() {
		$cocktailStepParamTypeTable = $this->getCocktailStepParamTypeTable();
		$resultSet = $cocktailStepParamTypeTable->fetchAll();
		$result=array();
		foreach ($resultSet as $row) {
			$result[$row->cocktail_step_param_type_id]=array($row->cocktail_step_param_type_table_name,$row->cocktail_step_param_type_table_pkey_name);
		}
		return $result;
	}
	
	public function findCocktailNamesByPartial($partialString) {
		$cocktailTable = $this->getCocktailTable();
		$select = $cocktailTable->newSelect();
		$cocktailTable->getSelect($select,array('cocktail_name'))->where->like('cocktail.cocktail_name','%'.$partialString.'%');
		$resultSet = $cocktailTable->selectWith($select);
		$result=array();
		foreach ($resultSet as $row) {
			array_push($result,$row->cocktail_name);
		}
		return $result;
	}
	
	public function deletecCocktailSteps($cocktailId) {
		$cocktailStepTable = $this->getCocktailSteptable();
		$select = $cocktailStepTable->newSelect();
		$cocktailStepTable->getSelect($select,array('cocktail_step_id'))
		->where(array('cocktail_id'=>$cocktailId));
		$resultSet = $cocktailStepTable->selectWith($select);
		$cocktailStepParamTable = $this->getCocktailStepParamTable();
		foreach ($resultSet as $row) {
			$cocktailStepParamTable->delete(array('cocktail_step_id'=>$row->cocktail_step_id));
		}
		$cocktailStepTable->delete(array('cocktail_id'=>$cocktailId));
	}
	
	public function deleteCocktail($cocktailname) {
		$cocktailId = $this->getCocktailId($cocktailname);
		if (!$cocktailId) {
			return false;
		}
		$this->deletecCocktailSteps($cocktailId);
		$this->getCocktailSourceRelationTable()->delete(array('cocktail_id'=>$cocktailId));
		$this->getCocktailTable()->delete(array('cocktail_id'=>$cocktailId));
		return true;
	}
	
	public function getCocktailsByUserId($userId) {
		$cocktailTable = $this->getCocktailTable();
		$select = $cocktailTable->newSelect();
		$cocktailTable->getSelect($select,array('cocktail_name','cocktail_timestamp'))
		->where(array('user_id'=>$userId));
		$resultSet = $cocktailTable->selectWith($select);
		return $resultSet->toArray();
	}

    public function findCocktailsByDrinkFlavorIds($drinkFavlorIds) {
        $select;
    }
	
}
